package br.com.cliquetroca.embedded;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class CreateTables {
	
	private static final String URL_PATH = "data_base/sample";

	private static final String URL = "jdbc:derby:data_base/sample;";
	private static Connection conn = null;
	private static Statement stmt = null;
	
	public static void main(String[] args) {
		try {
			prepare();
			createConnection();
			create();
			insert();
			select();
			shutDown();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void prepare() {
		deleteDirectory(new File(URL_PATH));
	}
	
	static public boolean deleteDirectory(File path) {
		if( path.exists() ) {
			File[] files = path.listFiles();
		    for(int i=0; i < files.length; i++) {
		    	if(files[i].isDirectory()) {
		    		deleteDirectory(files[i]);
		    	} else {
		           files[i].delete();
		    	}
		    }
		}
		return( path.delete() );
	}

	private static void shutDown() {
		try {
			if (stmt != null) {
				stmt.close();
			} if (conn != null) {
				DriverManager.getConnection(URL);
	            conn.close();
			}           
		} catch (SQLException sqlExcept) {}
	}

	private static void createConnection() throws SQLException {
		Properties prop = new Properties();
		prop.put("create", "true");
		prop.put("user", "sample");
		prop.put("password", "sample");
		conn = DriverManager.getConnection(URL, prop);
	}
	
	private static void create() {
		
		try {
			stmt = conn.createStatement();
			
			stmt.addBatch(Tabelas.USUARIO);
//			stmt.addBatch(Tabelas.CLIENTE);
			stmt.addBatch(Tabelas.TABLE_SEQUENCE);
			
			stmt.executeBatch();
	        stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}
	
	private static void insert() {
		Hasher hasher = new Hasher();
		try {
			String _insert = "insert into usuario values('%s','%s',%s)";
			String one = String.format(_insert, "admin", hasher.hash("123456"), 1);
			
			stmt = conn.createStatement();
	        stmt.execute(one);
	        stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}
	
	private static void select() {
		String select = "select * from usuario";
		try {
			stmt = conn.createStatement();
	        ResultSet results = stmt.executeQuery(select);
//	        ResultSetMetaData rsmd = results.getMetaData();
//	        int numberCols = rsmd.getColumnCount();
//	        for (int i=1; i<=numberCols; i++) {
//	        	System.out.print(rsmd.getColumnLabel(i)+"\t\t");
//	        }
	        
	        System.out.print("LOGIN		SENHA						STATUS");
	        
	        System.out.println("\n-----------------------------------------------------------------------");
	        
	        while(results.next()) {
	        	String login = results.getString(1);
	            String senha = results.getString(2);
	            int status = results.getInt(3);
	            System.out.println(login + "\t\t" + senha + "\t\t" + status);
	        }
	        results.close();
	        stmt.close();
		} catch (SQLException sqlExcept) {
			sqlExcept.printStackTrace();
		}
	}
}